﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DatabaseConnection
/// </summary>
public class DatabaseConnection
{
    string connectionString;
    SqlConnection connection;
    Account account;
    Contacts contacts;
    Friends friends;

	public DatabaseConnection()
	{
        connection = new SqlConnection("Data Source=.;Initial Catalog=MrMobileDB;Integrated Security=True"); // TO DO :dymnacilly 
        //Data Source=.\\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=AhmedHafez_mrmobilen
        account = new Account();
        contacts = new Contacts();
        friends = new Friends();
	}

    public bool verifyAccount(string username, string password)
    {
        connection.Open();
        SqlCommand verifyCommand = new SqlCommand("exec "+account.verifyAccount+" '"+username+"','"+password+"'",connection);
        SqlDataReader Read = verifyCommand.ExecuteReader();
        if(Read.Read())
        {
            connection.Close();
            return true;
        }
        else
        {
            connection.Close();
            return false;
        }
    }

    public bool insertAccount(string username, string password, string firstName, string lastName, string birthDate, string email, string Telephone, string country)
    {
        try
        {
            connection.Open();
            SqlCommand insertcommand = new SqlCommand("exec " + account.insertAccount + " '" + username + "','" + password + "','" + firstName + "','" + lastName + "','" + birthDate + "','" + email + "','" + Telephone + "','0','1','" + country + "'", connection);
            insertcommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool insertContact(string username, string contactName, string contactNumber)
    {
        try
        {
            connection.Open();
            SqlCommand insertContactCommand = new SqlCommand("exec " + contacts.insertContact + " '" + username + "','" + contactName + "','" + contactNumber + "'", connection);
            insertContactCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool selectContact(string username, string contactName)
    {
        connection.Open();
        SqlCommand selectContactCommand = new SqlCommand("exec "+contacts.selectContact+" '"+username+"','"+contactName+"'", connection);
        SqlDataReader Read = selectContactCommand.ExecuteReader();
        if (Read.Read())
        {
            connection.Close();
            return true;
        }
        else
        {
            connection.Close();
            return false;
        }
    }

    public string getContactNumber(string username, string contactName)
    {
        connection.Open();
        SqlCommand getContactNumberCommand = new SqlCommand("exec "+contacts.getContactNumber+" '"+username+"','"+contactName+"'", connection);
        SqlDataReader Read = getContactNumberCommand.ExecuteReader();
        if (Read.Read())
        {
            string x = Read[0].ToString();
            connection.Close();
            return x;
        }
        else
        {
            connection.Close();
            return "false";
        }
    }

    public int countContacts(string username)
    {
        connection.Open();
        SqlCommand countContactsCommand = new SqlCommand("exec "+contacts.countContacts+" '"+username+"'", connection);
        SqlDataReader Read = countContactsCommand.ExecuteReader();
        if (Read.Read())
        {
            int x = Int32.Parse(Read[0].ToString());
            connection.Close();
            return x;
        }
        else
        {
            connection.Close();
            return -1;
        }

    }

    public string getContactNameUsingID(string username, int id)
    {

        connection.Open();
        SqlCommand getContactNameUsingIDCommand = new SqlCommand("exec " + contacts.getContactNameUsingID + " '" + username + "','" + id + "'", connection);
        SqlDataReader Read = getContactNameUsingIDCommand.ExecuteReader();
        if (Read.Read())
        {
            string x = Read[0].ToString();
            connection.Close();
            return x;
        }
        else
        {
            connection.Close();
            return "false"; // assuming no contact name is allowed to be false !
        }
    }

    public bool updateContactName(string username, string oldContactName,string newContactName)
    {
        try
        {
            connection.Open();
            SqlCommand updateContactNameCommand = new SqlCommand("exec " + contacts.updateContactName + " '" + username + "','" + oldContactName + "','" + newContactName + "'", connection);
            updateContactNameCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool updateContactNumber(string username, string contactName, string newContactNumber)
    {
        try
        {
            connection.Open();
            SqlCommand updateContactNumberCommand = new SqlCommand("exec " + contacts.updateContactNumber + " '" + username + "','" + contactName + "','" + newContactNumber + "'", connection);
            updateContactNumberCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool deleteAccountUser(string username, string password)
    {
        if (deleteAccountFromBeingAFriend(username))
        {
            if (deleteAccountFriends(username))
            {
                if (deleteAccountContacts(username))
                {
                    try
                    {
                        connection.Open();
                        SqlCommand deleteAccountUserCommand = new SqlCommand("exec " + account.deleteAccountUser + " '" + username + "','" + password + "'", connection);
                        deleteAccountUserCommand.ExecuteNonQuery();
                        connection.Close();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        connection.Close();
                        return false;
                    }
                }
                else
                {
                    return false;
                }

            }
            else
            {
                return false;
            }

        }
        else
        {
            return false;
        }

    }

    public bool deleteAccountAdmin(string username)
    {

        if (deleteAccountFromBeingAFriend(username))
        {
            if (deleteAccountFriends(username))
            {
                if (deleteAccountContacts(username))
                {
                    try
                    {
                        connection.Open();
                        SqlCommand deleteAccountAdminCommand = new SqlCommand("exec " + account.deleteAccountAdmin + " '" + username + "'", connection);
                        deleteAccountAdminCommand.ExecuteNonQuery();
                        connection.Close();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        connection.Close();
                        return false;
                    }
                }
                else
                {
                    return false;
                }

            }
            else
            {
                return false;
            }

        }
        else
        {
            return false;
        }

    }

    #region delete_Account_referential

    public bool deleteAccountContacts(string username)
    {
        try
        {
            connection.Open();
            SqlCommand deleteAccountContactsCommand = new SqlCommand("exec " + account.deleteAccountContacts + " '" + username + "'", connection);
            deleteAccountContactsCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    } //used to ensure the referential integrity after deleting an account

    public bool deleteAccountFriends(string username)
    {
        try
        {
            connection.Open();
            SqlCommand deleteAccountFriendsCommand = new SqlCommand("exec " + account.deleteAccountFriends + " '" + username + "'", connection);
            deleteAccountFriendsCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }//used to ensure the referential integrity after deleting an account

    public bool deleteAccountFromBeingAFriend(string username)
    {
        try
        {
            connection.Open();
            SqlCommand deleteAccountFromBeingAFriendCommand = new SqlCommand("exec " + account.deleteAccountFromBeingAFriend + " '" + username + "'", connection);
            deleteAccountFromBeingAFriendCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }//used to ensure the referential integrity after deleting an account

    #endregion delete_Account_referential

    public bool deleteContact(string username, string contactName)
    {
        try
        {
            connection.Open();
            SqlCommand deleteContactCommand = new SqlCommand("exec " + contacts.deleteContact + " '" + username + "','" + contactName + "'", connection);
            deleteContactCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public SqlDataReader selectContacts(string username)
    {
        connection.Open();
        SqlCommand selectContactsCommand = new SqlCommand("exec " + contacts.selectContacts + " '" + username + "'", connection);
        SqlDataReader Read = selectContactsCommand.ExecuteReader();
        connection.Close();
        return Read;
    }

    public SqlDataReader selectFriends(string username)
    {
        connection.Open();
        SqlCommand selectFriendsCommand = new SqlCommand("exec " + friends.selectFriends + " '" + username + "'", connection);
        SqlDataReader Read = selectFriendsCommand.ExecuteReader();
        connection.Close();
        return Read;
    }


    public bool editFriend(string username, string friendUsername, int shareOption)
    {
        //this stored procedure returns an integerr, what is it ???
        try
        {
            connection.Open();
            SqlCommand editFriendCommand = new SqlCommand("exec " + friends.editFriend + " '" + username + "','" + friendUsername + "','" + shareOption + "'", connection);
            editFriendCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool addFriend(string username, string friendUsername)
    {
        try
        {
            connection.Open();
            SqlCommand addFriendCommand = new SqlCommand("exec " + friends.addFriend + " '" + username + "','" + friendUsername + "'", connection);
            addFriendCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool deleteFriend(string username, string friendUsername)
    {
        try
        {
            connection.Open();
            SqlCommand deleteFriendCommand = new SqlCommand("exec " + friends.deleteFriend + " '" + username + "','" + friendUsername + "'", connection);
            deleteFriendCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public string getPassword(string email)
    {

        connection.Open();
        SqlCommand getPasswordCommand = new SqlCommand("exec " + account.getPassword + " '" + email+"'", connection);
        SqlDataReader Read = getPasswordCommand.ExecuteReader();
        if (Read.Read())
        {
            string x = Read[0].ToString();
            connection.Close();
            return x;
        }
        else
        {
            connection.Close();
            return null; // TO DO:  changed
        }
    }


    public string getEmail(string username)
    {

        connection.Open();
        SqlCommand getEmailCommand = new SqlCommand("exec " + account.getEmail + " '" + username+"'", connection);
        SqlDataReader Read = getEmailCommand.ExecuteReader();
        if (Read.Read())
        {
            string x = Read[0].ToString();
            connection.Close();
            return x;
        }
        else
        {
            connection.Close();
            return null; // TO DO:  changed
        }
    }


    #region update_Account_info
    public bool updateTelephone(string username, string newtelephone)
    {
        try
        {
            connection.Open();
            SqlCommand updateTelephoneCommand = new SqlCommand("exec " + account.updateTelephone + " '" + username + "','" + newtelephone + "'", connection);
            updateTelephoneCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool updateUsername(string username, string newusername)
    {
        try
        {
            connection.Open();
            SqlCommand updateUsernameCommand = new SqlCommand("exec " + account.updateUsername + " '" + username + "','" + newusername + "'", connection);
            updateUsernameCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
 
    }

    public bool updatePassword(string username, string newpassword)
    {

        try
        {
            connection.Open();
            SqlCommand updatePasswordCommand = new SqlCommand("exec " + account.updatePassword + " '" + username + "','" + newpassword + "'", connection);
            updatePasswordCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
 
    }

    public bool updateEmail(string username, string newemail)
    {

        try
        {
            connection.Open();
            SqlCommand updateEmailCommand = new SqlCommand("exec " + account.updateEmail + " '" + username + "','" + newemail + "'", connection);
            updateEmailCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }

    public bool updateBirthdate(string username, string newbirthdate)
    {

        try
        {
            connection.Open();
            SqlCommand updateBirthdateCommand = new SqlCommand("exec " + account.updateBirthDate + " '" + username + "','" + newbirthdate + "'", connection);
            updateBirthdateCommand.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            connection.Close();
            return false;
        }
    }
    #endregion update_Account_info

}